﻿using API_Service.DBHelper;
using Dapper;
using System;
using System.Data;
using System.Linq;
using System.Reflection;

namespace API_Service.BizLogic.BasicData
{
    public class BomDataService : BaseLogicService
    {
        private string _erpConnString = "";
        public BomDataService()
        {
            _erpConnString = SqlCreator.ConnectionStrings["yfdb"];
        }
        #region Bom Master
        public ExecutionResult<dynamic> SyncBomMasterDataFromERP()
        {
            ExecutionResult<dynamic> result = new ExecutionResult<dynamic>();
            try
            {
                string _strMaxCreateTime = "", _strMaxUpdateTime = "";
                //获取MES系统中当前物料最后更新时间。
                result = this.GetMESLastBomMasterERPData();
                if (result.Status)
                {
                    var _tmpDate = result.Datas.AsList()[0];
                    _strMaxCreateTime = String.IsNullOrEmpty(_tmpDate.erp_create_time) ? "19990101" : _tmpDate.erp_create_time;
                    _strMaxUpdateTime = String.IsNullOrEmpty(_tmpDate.erp_update_time) ? "19990101" : _tmpDate.erp_update_time;
                    
                    //获取ERP更新数据
                    result = this.QueryERPNewBomMasterInfo(_strMaxCreateTime, _strMaxUpdateTime);
                    if (result.Status)
                    {
                        //循环取数，并更新MES库数据
                        foreach (var _tmpMat in result.Datas.ToList())
                        {
                            bool _bExists = false;
                            //BomMasterInfo _tmpMat = (BomMasterInfo)mat;
                            //check mo is exists
                            result = this.QueryBomMasterInfo(_tmpMat.Bom_No);
                            if (result.Status)
                            {
                                if (result.Datas.Length>0)
                                {
                                    var matId = result.Datas.ToList()[0].id;
                                    var matFlag = result.Datas.ToList()[0].is_valid;
                                    if (matFlag == "N")
                                    {
                                        continue;
                                    }
                                    //update
                                    _tmpMat.Update_User = "SYSTEM_JOB";
                                    //_tmpMat.Update_Time = DateTime.Now;
                                    _tmpMat.Id = matId;
                                    result = this.UpdateBomMasterInfo(_tmpMat);
                                }
                                else
                                {
                                    //insert 
                                    _tmpMat.Create_User = "SYSTEM_JOB";
                                    //_tmpMat.Create_Time = DateTime.Now;
                                    result = this.InsertBomMasterInfo(_tmpMat);
                                }
                                //var matId = result.Datas.ToList()[0].id;
                                //var matFlag = result.Datas.ToList()[0].is_valid;
                                //if (matFlag == "N")
                                //{
                                //    continue;
                                //}
                                //_bExists = matId > 0 ? true : false;
                                //if (_bExists)
                                //{
                                //    //update
                                //    _tmpMat.Update_User = "SYSTEM_JOB";
                                //    //_tmpMat.Update_Time = DateTime.Now;
                                //    _tmpMat.Id = matId;
                                //    result = this.UpdateBomMasterInfo(_tmpMat);
                                //}
                                //else
                                //{
                                //    //insert 
                                //    _tmpMat.Create_User = "SYSTEM_JOB";
                                //    //_tmpMat.Create_Time = DateTime.Now;
                                //    result = this.InsertBomMasterInfo(_tmpMat);
                                //}
                            }
                            if (!result.Status)
                            {
                                return result;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);

                result.Status = false;
                result.Message = ex.Message;
            }

            return result;
        }
        public ExecutionResult<dynamic> GetMOBaseList()
        {
            var result = new ExecutionResult<dynamic>();

            return result;
        }
        private ExecutionResult<dynamic> GetMESLastBomMasterERPData()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = "SELECT max(erp_create_time) erp_create_time,max(erp_update_time) erp_update_time FROM dbo.c_bom_keyparts_t WHERE is_valid = 'Y'";
                    conn.Open();
                    result.Datas = conn.Query(sql).ToArray();

                    result.Message = "获取数据更新时间成功";
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> QueryBomMasterInfo(string vBOM = null, string vIsValid = null)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @"  SELECT TOP 1000 [id]
                                  ,[bom_no]
                                  ,[key_part_no]
                                  ,[bom_version]
                                  ,[qty]
                                  ,[unit]
                                  ,[wastage_rate]
                                  ,[alternative]
                                  ,[sequence]
                                  ,[operation]
                                  ,[designator_location]
                                  ,[remarks]
                                  ,[erp_create_time]
                                  ,[erp_update_time]
                                  ,[is_valid]
                                  ,[create_time]
                                  ,[create_user]
                                  ,[update_time]
                                  ,[update_user]
                              FROM [scumes].[dbo].[c_bom_keyparts_t] WHERE 1=1 ";
                    if (!String.IsNullOrEmpty(vBOM))
                    {
                        sql = sql + " AND [BOM_NO] = @bomno";
                    }
                    if (!String.IsNullOrEmpty(vIsValid))
                    {
                        sql = sql + " AND [is_valid] = @isValid";
                    }

                    result.Datas = conn.Query(sql, new { mo = vBOM, isValid = vIsValid }).ToArray();
                    result.Status = true;
                    result.Message = "查询BOM信息成功.";
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询BOM基础信息异常。信息:" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> InsertBomMasterInfo(dynamic _tmpMat)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                //var _tmpMat = (BomMasterInfo)vModel;
                try
                {
                    string _insertSql = @"   insert into [dbo].[c_bom_keyparts_t]( 
       [bom_no]      ,[key_part_no]      ,[bom_version]      ,[qty]      ,[unit]      ,[wastage_rate]      ,[alternative]
      ,[sequence]      ,[operation]      ,[designator_location]      ,[remarks]      ,[erp_create_time]      ,[erp_update_time]
      ,[is_valid]      ,[create_time]      ,[create_user])
            values(@bom_no,@key_part_no,@bom_version,@qty,@unit,@wastage_rate,@alternative,@sequence
            ,@operation,@designator_location,@remarks,@erp_create_time,@erp_update_time,@is_valid,@create_time,@create_user) ";
                    var dbParams = new DynamicParameters();

                    //dbParams.Add("@id", _tmpMat.Id, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@bom_no", _tmpMat.bom_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@key_part_no", _tmpMat.key_part_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@bom_version", _tmpMat.bom_version, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@qty", _tmpMat.qty, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@unit", _tmpMat.unit, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@wastage_rate", _tmpMat.wastage_rate, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@alternative", _tmpMat.alternative, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@sequence", _tmpMat.sequence, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@operation", _tmpMat.operation, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@designator_location", _tmpMat.designator_location, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@remarks", _tmpMat.remarks, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_create_time", _tmpMat.erp_create_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_update_time", _tmpMat.erp_update_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@is_valid", _tmpMat.is_valid, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@create_time", DateTime.Now, DbType.DateTime, ParameterDirection.Input);
                    dbParams.Add("@create_user", _tmpMat.Create_User, DbType.String, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "添加BOM信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "添加BOM信息失败";
                    }
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "添加BOM信息异常:"+ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> UpdateBomMasterInfo(dynamic _tmpMat)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                //var _tmpMat = (BomMasterInfo)vModel;
                try
                {
                    string _insertSql = @"  
                                  UPDATE [dbo].[c_bom_keyparts_t]
                                  SET [bom_no]=@bom_no
                                      ,[key_part_no]=@key_part_no
                                      ,[bom_version]=@bom_version
                                      ,[qty]=@qty
                                      ,[unit]=@unit
                                      ,[wastage_rate]=@wastage_rate
                                      ,[alternative]=@alternative
                                      ,[sequence]=@sequence
                                      ,[operation]=@operation
                                      ,[designator_location]=@designator_location
                                      ,[remarks]=@remarks
                                      ,[erp_create_time]=@erp_create_time
                                      ,[erp_update_time]=@erp_update_time
                                      ,[is_valid]=@is_valid
                                      --,[create_time]=@create_time
                                      --,[create_user]=@create_user
                                      ,[update_time]=@update_time
                                      ,[update_user]=@update_user
                                  WHERE [id]=@id   ";
                    var dbParams = new DynamicParameters();

                    dbParams.Add("@id", _tmpMat.Id, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@bom_no", _tmpMat.bom_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@key_part_no", _tmpMat.key_part_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@bom_version", _tmpMat.bom_version, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@qty", _tmpMat.qty, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@unit", _tmpMat.unit, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@wastage_rate", _tmpMat.wastage_rate, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@alternative", _tmpMat.alternative, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@sequence", _tmpMat.sequence, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@operation", _tmpMat.operation, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@designator_location", _tmpMat.designator_location, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@remarks", _tmpMat.remarks, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_create_time", _tmpMat.erp_create_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_update_time", _tmpMat.erp_update_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@is_valid", _tmpMat.is_valid, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@update_time", DateTime.Now, DbType.DateTime, ParameterDirection.Input);
                    dbParams.Add("@update_user", _tmpMat.Update_User, DbType.String, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "更新BOM信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "更新BOM信息失败";
                    }
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "更新BOM信息异常:"+ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> DeleteMoBaseInfo(int vId)
        {
            var result = new ExecutionResult<dynamic>();

            string _insertSql = @"DELETE FROM [dbo].[r_mo_base_t] WHERE[id] = @id";
            var dbParams = new DynamicParameters();
            dbParams.Add("@id", vId, DbType.Int32, ParameterDirection.Input);

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "删除工单信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "删除工单信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "删除工单信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> QueryERPNewBomMasterInfo(string vCreateTime, string vUpdateTime)
        {
            var result = new ExecutionResult<dynamic>();
            string sql = @" SELECT TOP 300 c.MC001 as bom_no,d.MD003 as key_part_no, c.MC009 as bom_version,d.MD006 as qty,d.MD004 as unit,
                d.MD008 as wastage_rate,d.MD010 as alternative ,d.MD002 as sequence,d.MD009 as operaton,d.MD015 as designator_location,
                c.MC010 as remarks, c.CREATE_DATE as erp_create_time,c.MODI_DATE as erp_update_time 
                FROM dbo.BOMMC c left join dbo.BOMMD d on c.MC001=d.MD001
                WHERE (c.CREATE_DATE >@CREATE_DATE or c.MODI_DATE >@UPDATE_DATE) 
                AND c.MC019 = 'Y' ";
            var dbParams = new DynamicParameters();
            dbParams.Add("@CREATE_DATE", vCreateTime, DbType.String, ParameterDirection.Input);
            dbParams.Add("@UPDATE_DATE", vUpdateTime, DbType.String, ParameterDirection.Input);
            //链接ERP DB，查询物料信息
            using (IDbConnection conn = SqlCreator.Create(DataBaseType.SqlServer,this._erpConnString))
            {
                try
                {                    
                    result.Datas = conn.Query<dynamic>(sql, dbParams).ToArray();
                    result.Status = true;
                    result.Message = "Get ERP Bom Master data success.";
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + result.Message);
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }

        #endregion

        #region Bom alternative
        public ExecutionResult<dynamic> SyncBomAlterDataFromERP()
        {
            ExecutionResult<dynamic> result = new ExecutionResult<dynamic>();
            try
            {
                string _strMaxCreateTime = "", _strMaxUpdateTime = "";
                //获取MES系统中当前物料最后更新时间。
                result = this.GetMESLastBomAlterERPData();
                if (result.Status)
                {
                    var _tmpDate = result.Datas.AsList()[0];
                    _strMaxCreateTime = String.IsNullOrEmpty(_tmpDate.erp_create_time) ? "19990101" : _tmpDate.erp_create_time;
                    _strMaxUpdateTime = String.IsNullOrEmpty(_tmpDate.erp_update_time) ? "19990101" : _tmpDate.erp_update_time;

                    //获取ERP更新数据
                    result = this.QueryERPNewBomAlterInfo(_strMaxCreateTime, _strMaxUpdateTime);
                    if (result.Status)
                    {
                        //循环取数，并更新MES库数据
                        foreach (var _tmpMat in result.Datas.ToList())
                        {
                            bool _bExists = false;
                            //BomAlterInfo _tmpMat = (BomAlterInfo)mat;
                            //check mo is exists
                            result = this.QueryBomAlterInfo(_tmpMat.Bom_No);
                            if (result.Status)
                            {
                                if (result.Datas.Length>0)
                                {
                                    var matId = result.Datas.ToList()[0].id;
                                    var matFlag = result.Datas.ToList()[0].is_valid;
                                    if (matFlag == "N")
                                    {
                                        continue;
                                    }
                                    //update
                                    _tmpMat.Update_User = "SYSTEM_JOB";
                                    //_tmpMat.Update_Time = DateTime.Now;
                                    _tmpMat.Id = matId;
                                    result = this.UpdateBomAlterInfo(_tmpMat);
                                }
                                else
                                {
                                    //insert 
                                    _tmpMat.Create_User = "SYSTEM_JOB";
                                    //_tmpMat.Create_Time = DateTime.Now;
                                    result = this.InsertBomAlterInfo(_tmpMat);
                                }

                                //var matId = result.Datas.ToList()[0].id;
                                //var matFlag = result.Datas.ToList()[0].is_valid;
                                //if (matFlag == "N")
                                //{
                                //    continue;
                                //}
                                //_bExists = matId > 0 ? true : false;
                                //if (_bExists)
                                //{
                                //    //update
                                //    _tmpMat.Update_User = "SYSTEM_JOB";
                                //    //_tmpMat.Update_Time = DateTime.Now;
                                //    _tmpMat.Id = matId;
                                //    result = this.UpdateBomAlterInfo(_tmpMat);
                                //}
                                //else
                                //{
                                //    //insert 
                                //    _tmpMat.Create_User = "SYSTEM_JOB";
                                //    //_tmpMat.Create_Time = DateTime.Now;
                                //    result = this.InsertBomAlterInfo(_tmpMat);
                                //}
                            }
                            if (!result.Status)
                            {
                                return result;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);

                result.Status = false;
                result.Message = ex.Message;
            }

            return result;
        }

        private ExecutionResult<dynamic> GetMESLastBomAlterERPData()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = "SELECT max(erp_create_time) erp_create_time,max(erp_update_time) erp_update_time FROM dbo.c_bom_alternative_t WHERE is_valid = 'Y'";
                    conn.Open();
                    result.Datas = conn.Query(sql).ToArray();

                    result.Message = "获取数据更新时间成功";
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
            }

            return result;
        }
        private ExecutionResult<dynamic> QueryBomAlterInfo(string vBOM = null, string vIsValid = null)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @"  SELECT TOP 1000 [id]
                                      ,[bom_no]
                                      ,[key_part_no]
                                      ,[alternative_kpn]
                                      ,[replace_item]
                                      ,[unit]
                                      ,[qty]
                                      ,[sequence]
                                      ,[effective_date]
                                      ,[expiry_date]
                                      ,[remarks]
                                      ,[erp_create_time]
                                      ,[erp_update_time]
                                      ,[is_valid]
                                      ,[create_time]
                                      ,[create_user]
                                      ,[update_time]
                                      ,[update_user]
                                  FROM [scumes].[dbo].[c_bom_alternative_t] WHERE 1=1 ";
                    if (!String.IsNullOrEmpty(vBOM))
                    {
                        sql = sql + " AND [BOM_NO] = @bomno";
                    }
                    if (!String.IsNullOrEmpty(vIsValid))
                    {
                        sql = sql + " AND [is_valid] = @isValid";
                    }

                    result.Datas = conn.Query(sql, new { mo = vBOM, isValid = vIsValid }).ToArray();
                    result.Status = true;
                    result.Message = "查询BOM替代信息成功.";
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询BOM替代信息异常。信息:" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> InsertBomAlterInfo(dynamic _tmpMat)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                //var _tmpMat = (BomAlterInfo)vModel;
                try
                {
                    string _insertSql = @"   insert INTO [dbo].[c_bom_alternative_t]([bom_no]
      ,[key_part_no]      ,[alternative_kpn]      ,[replace_item]      ,[unit]      ,[qty]      ,[sequence]      ,[effective_date]
      ,[expiry_date]      ,[remarks]      ,[erp_create_time]      ,[erp_update_time]      ,[is_valid]      ,[create_time]      ,[create_user])
	 VALUES(@bom_no,@key_part_no,@alternative_kpn,@replace_item,@unit,@qty,@sequence,@effective_date,@expiry_date,
        @remarks,@erp_create_date,@erp_update_date,@is_valid,@create_time,@create_user,@update_time,@update_user) ";
                    var dbParams = new DynamicParameters();

                    //dbParams.Add("@id", _tmpMat.Id, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@bom_no", _tmpMat.bom_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@key_part_no", _tmpMat.key_part_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@alternative_kpn", _tmpMat.alternative_kpn, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@qty", _tmpMat.qty, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@unit", _tmpMat.unit, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@replace_item", _tmpMat.replace_item, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@sequence", _tmpMat.sequence, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@effective_date", _tmpMat.effective_date, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@expiry_date", _tmpMat.expiry_date, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@remarks", _tmpMat.remarks, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_create_time", _tmpMat.erp_create_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_update_time", _tmpMat.erp_update_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@is_valid", _tmpMat.is_valid, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@create_time", DateTime.Now, DbType.DateTime, ParameterDirection.Input);
                    dbParams.Add("@create_user", _tmpMat.Create_User, DbType.String, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "添加BOM替代信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "添加BOM替代信息失败";
                    }
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "添加BOM替代信息异常:"+ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> UpdateBomAlterInfo(dynamic _tmpMat)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                //var _tmpMat = (BomAlterInfo)vModel;
                try
                {
                    string _insertSql = @"  
                                  UPDATE [dbo].[c_bom_alternative_t]
                                    SET bom_no=@bom_no
                                    ,key_part_no=@key_part_no
                                    ,alternative_kpn=@alternative_kpn
                                    ,replace_item=@replace_item
                                    ,unit=@unit
                                    ,qty=@qty
                                    ,sequence=@sequence
                                    ,effective_date=@effective_date
                                    ,expiry_date=@expiry_date
                                    ,remarks=@remarks
                                    ,erp_create_time=@erp_create_time
                                    ,erp_update_time=@erp_update_time
                                    ,is_valid=@is_valid
                                    --,create_time=@create_time
                                    --,create_user=@create_user
                                    ,update_time=@update_time
                                    ,update_user=@update_user
                                    WHERE id=@id  ";
                    var dbParams = new DynamicParameters();

                    dbParams.Add("@id", _tmpMat.Id, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@bom_no", _tmpMat.bom_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@key_part_no", _tmpMat.key_part_no, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@alternative_kpn", _tmpMat.alternative_kpn, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@qty", _tmpMat.qty, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@unit", _tmpMat.unit, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@replace_item", _tmpMat.replace_item, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@sequence", _tmpMat.sequence, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@effective_date", _tmpMat.effective_date, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@expiry_date", _tmpMat.expiry_date, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@remarks", _tmpMat.remarks, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_create_time", _tmpMat.erp_create_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@erp_update_time", _tmpMat.erp_update_time, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@is_valid", _tmpMat.is_valid, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@update_time", DateTime.Now, DbType.DateTime, ParameterDirection.Input);
                    dbParams.Add("@update_user", _tmpMat.Update_User, DbType.String, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "更新BOM替代表信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "更新BOM替代表信息失败";
                    }
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "更新BOM替代表信息异常+"+ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> QueryERPNewBomAlterInfo(string vCreateTime, string vUpdateTime)
        {
            var result = new ExecutionResult<dynamic>();
          string sql = @"   select top 300 a.MA002 as bom_no,a.MA001 as key_part_no,b.MB004 as alternative_kpn,b.MB003 as replace_item,b.MB005 as unit,
                        b.MB009 as sequence,b.MB006 as effective_date,b.MB007 as expiry_date,b.MB008 as remarks,b.CREATE_DATE as ERP_Create_Time,
                  b.MODI_DATE as ERP_Update_Time
                  from  dbo.BOMMA a left join dbo.BOMMB b on a.MA001=b.MB001 and a.MA002=b.MB002 and a.MA003=b.MB003
                WHERE (b.CREATE_DATE >@CREATE_DATE or b.MODI_DATE >@UPDATE_DATE) ";
            var dbParams = new DynamicParameters();
            dbParams.Add("@CREATE_DATE", vCreateTime, DbType.String, ParameterDirection.Input);
            dbParams.Add("@UPDATE_DATE", vUpdateTime, DbType.String, ParameterDirection.Input);
            //链接ERP DB，查询物料信息
            using (IDbConnection conn = SqlCreator.Create(DataBaseType.SqlServer,this._erpConnString))
            {
                try
                {                    
                    result.Datas = conn.Query<dynamic>(sql, dbParams).ToArray();
                    result.Status = true;
                    result.Message = "Get ERP Bom Alter data success.";
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + result.Message);
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }

        #endregion

        #region BOM Check
        public ExecutionResult<dynamic> CheckKPSInBOMList(string vBomNo,string vKPN,string vGroupName)
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            if(String.IsNullOrEmpty(vBomNo) || String.IsNullOrEmpty(vKPN))
            {
                result.Status = false;
                result.Message = "BOM NO及料号不可为空";

                return result;
            }
            #region sql and parameter
            string sql = @"SELECT
	                                        a.bom_no,
	                                        a.key_part_no,
	                                        a.qty,
	                                        a.unit,
	                                        a.part_code,
	                                        b.alternative_kpn,
	                                        b.sequence
                                        FROM [dbo].[c_group_kpn_code_cfg_t] as c,
	                                        [dbo].[c_bom_keyparts_t] AS a
                                        LEFT JOIN [dbo].[c_bom_alternative_t] AS b ON a.bom_no = b.bom_no
                                        AND a.key_part_no = b.key_part_no
                                        WHERE c.kpn_code = a.part_code AND c.group_name=@group_name AND
	                                        a.bom_no = @bom_no
                                        AND (
	                                        a.key_part_no = @key_part_no
	                                        OR b.alternative_kpn = @key_part_no
                                        )";
            DynamicParameters dbParams = new DynamicParameters();
            dbParams.Add("@bom_no", vBomNo, DbType.String, ParameterDirection.Input);
            dbParams.Add("@key_part_no", vKPN, DbType.String, ParameterDirection.Input);
            dbParams.Add("@group_name", vGroupName, DbType.String, ParameterDirection.Input);
            #endregion 
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    result.Datas = conn.Query(sql, dbParams).ToArray();
                    if (result.Datas.Length > 0)
                    {
                        result.Status = true;
                        result.Message = "BOM Check OK.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = String.Format("KPN[{0}]不在BOM中", vKPN);
                    }
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "检查BOM资料时系统异常，异常信息：" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
                return result;
        }

        public ExecutionResult<dynamic> QueryGroupKPNPickListInBom(string vGroupName,string vBomNo)
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            #region 
            string sql = @"SELECT  a.bom_no,  a.key_part_no,  d.type,  d.spec,
	                                    a.qty,  a.unit, a.part_code, a.bom_version, b.alternative_kpn,
	                                    b.sequence
                                    FROM [dbo].[c_group_kpn_code_cfg_t] as c,[dbo].[c_material_t] as d,
	                                    [dbo].[c_bom_keyparts_t] AS a
                                    LEFT JOIN [dbo].[c_bom_alternative_t] AS b ON a.bom_no = b.bom_no
                                    AND a.key_part_no = b.key_part_no
                                    WHERE c.kpn_code = a.part_code AND a.key_part_no = d.key_part_no
                                        AND c.group_name=@group_name AND  a.bom_no = @bom_no";
            DynamicParameters dbParams = new DynamicParameters();
            dbParams.Add("@bom_no", vBomNo, DbType.String, ParameterDirection.Input);            
            dbParams.Add("@group_name", vGroupName, DbType.String, ParameterDirection.Input);
            #endregion
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    result.Datas = conn.Query(sql, dbParams).ToArray();
                    result.Status = true;
                    result.Message = "获取站点捡料信息成功";

                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "检查BOM资料时系统异常，异常信息：" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }
            return result;
        }

        #endregion
    }

    public class BomMasterInfo
    {
        public int Id { get; set; }
        public string Bom_No { get; set; }
        public string Key_Part_No { get; set; }
        public string Bom_Version { get; set; }
        public string Qty { get; set; }
        public string Unit { get; set; }
        public string Wastage_Rate { get; set; }
        public string Alternative { get; set; }
        public string Sequence { get; set; }
        public string Operation { get; set; }
        public string Designator_Location { get; set; }      
        public string Remarks { get; set; }
        public string Erp_Create_Time { get; set; }
        public string Erp_Update_Time { get; set; }
        private string _isValid = "Y";
        public string Is_Valid
        {
            get { return this._isValid; }
            set { this._isValid = String.IsNullOrEmpty(value) ? this._isValid : value; }
        }
        private string _sCreateUser = "SYSTEM_JOB";
        public string Create_User
        {
            get { return this._sCreateUser; }
            set { this._sCreateUser = String.IsNullOrEmpty(value) ? this._sCreateUser : value; }
        }
        public DateTime Create_Time { get; set; }

        private string _sUpdateUser = "SYSTEM_JOB";
        public string Update_User
        {
            get { return this._sUpdateUser; }
            set { this._sUpdateUser = String.IsNullOrEmpty(value) ? this._sUpdateUser : value; }
        }
        public DateTime Update_Time { get; set; }
    }


    public class BomAlterInfo
    {
        public int Id { get; set; }
        public string Bom_No { get; set; }
        public string Key_Part_No { get; set; }
        public string Alternative_Kpn { get; set; }
        public string Replace_Item { get; set; }
        public string Unit { get; set; }
        public string Qty { get; set; }
        public string Sequence { get; set; }
        public string Effective_Date { get; set; }
        public string Expiry_Date { get; set; }
        public string Remarks { get; set; }
        public string Erp_Create_Time { get; set; }
        public string Erp_Update_Time { get; set; }
        private string _isValid = "Y";
        public string Is_Valid
        {
            get { return this._isValid; }
            set { this._isValid = String.IsNullOrEmpty(value) ? this._isValid : value; }
        }
        private string _sCreateUser = "SYSTEM_JOB";
        public string Create_User
        {
            get { return this._sCreateUser; }
            set { this._sCreateUser = String.IsNullOrEmpty(value) ? this._sCreateUser : value; }
        }
        public DateTime Create_Time { get; set; }

        private string _sUpdateUser = "SYSTEM_JOB";
        public string Update_User
        {
            get { return this._sUpdateUser; }
            set { this._sUpdateUser = String.IsNullOrEmpty(value) ? this._sUpdateUser : value; }
        }
        public DateTime Update_Time { get; set; }
    }


}